// Copyright 2009 StarCite Inc.  All rights reserved.
// C3p0Pool.java 3:48:51 PM danny.liu
package com.billowsoft.sample.jdbc.pool;

import java.beans.PropertyVetoException;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * @author danny.liu May 30, 2012
 * @version 1.0
 * 
 */
public class C3p0Pool {

	private static ComboPooledDataSource dataSource;

	private static List<Connection> unClosedConnections = new ArrayList<Connection>();

	static {
		try {
			PropertyUtil.loadProperties("config.properties");
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		
		dataSource = new ComboPooledDataSource();
		try {
			dataSource.setDriverClass(System.getProperty("ds.driverClass"));
			dataSource.setJdbcUrl(System.getProperty("ds.url"));
			dataSource.setUser(System.getProperty("ds.username"));
			dataSource.setPassword(System.getProperty("ds.pwd"));
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
	}

	public static void showStatus() throws SQLException {
		System.out.println("Total connection: "
				+ dataSource.getNumConnections());
		System.out.println("Busy connection: "
				+ dataSource.getNumBusyConnections());
		System.out.println("Idle connection: "
				+ dataSource.getNumIdleConnections());
	}

	public static void createConnection(boolean isReleaseAfterUse)
			throws Exception {

		Connection conn = dataSource.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("select 'success'");
		while (rs.next()) {
			System.out.println("Result: " + rs.getString(1));
		}
		stmt.close();
		if (isReleaseAfterUse) {
			conn.close();
		} else {
			unClosedConnections.add(conn);
		}
	}

	public static void closeAllConnections() throws Exception {
		for (Connection conn : unClosedConnections) {
			conn.close();
		}
	}

}
